#!/usr/bin/python
# coding=utf-8

from sqlalchemy.types import VARCHAR, DECIMAL
import pandas as pd
import datetime
from common import df_operator as op, db_utils as util
import sys


def insertMO0102(p_month):
    P_START_TIME = """TO_CHAR(LAST_DAY(to_date(""" + p_month + """, 'yyyymm') - 4 months), 'yyyymmdd')||'200000'"""
    P_END_TIME = """TO_CHAR(LAST_DAY(to_date(""" + p_month + """, 'yyyymm') - 1 months), 'yyyymmdd')||'200000'"""
    sql = """DELETE FROM BGTARAS1.T_ADS_FACT_SICB_MO0102 WHERE DATE=""" + p_month
    conn_rds.execute(sql)
    sql_1 = """select IND,CALC_LOGIC from BGTARAS1.T_ADS_WH_SICB_MO0102 ORDER BY SEQ"""
    sql_result = util.query(conn_rds, sql=sql_1)
    if sql_result.__len__() > 0:
        for i in sql_result['SQL'].values:
            a = i.replace("'P_START_TIME'", P_START_TIME)
            b = a.replace("'P_END_TIME'", P_END_TIME)
            c = b.replace("'P_MONTH'", p_month)
            # print(c)
            df_result_1 = util.query(conn_mpp, sql=c)
            util.insert(df_result_1, conn_rds, "T_ADS_FACT_SICB_MO0102", "BGTARAS1")

    # 成材率 能耗和辅料
    sql1 = """
    SELECT
            '1001'                                                   AS ACCT,
            LEFT(A.COST_CENTER,2)                                       AS DEPARTMENT_CODE,
            A.COST_CENTER                                               AS COST_CENTER,
            A.UNIT                                                     AS UNIT_CODE,
            """ + p_month + """                                           AS DATE,
            A.ST_NO                                                     AS STEELNO,
            A.THICK,
            A.THICK_COD                                         AS THICK_CODE,
            A.WIDTH,
            A.WIDTH_COD                                          AS WIDTH_CODE,
            A.KEY_VAL                                      AS ENTRY_THICK,
            A.KEY_CODE                                     AS ENTRY_THICK_CODE,
            A.EDITION                                 AS IND,
            A.EDITION_NAME                                     AS IND_NAME,
            A.EDITION_UNIT                                            AS IND_UNIT,
            ROUND(AVG(A.EDITION_VALUE_FACT),3)                INDEX_VALUE,
            B.TARGET_RANGE_UL                                     AS TARGET_RANGE_UL,
            B.TARGET_RANGE_LL                                   AS TARGET_RANGE_LL,
             'admin'                                     AS REC_REVISOR,
            TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME,
            'admin' AS REC_CREATOR,
            TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
            ROUND(AVG(A.EDITION_VALUE_FACT),3)                REVISE_VALUE
        FROM
            BGTAMAS1.V_SU_AJBG_MO0101 A
        LEFT JOIN
            BGTAMAS1.T_ADS_FACT_SICB_MO0102 B
        ON
            A.UNIT=B.UNIT_CODE
        AND A.EDITION=B.IND
        AND B.DATE=TO_CHAR(TO_DATE(""" + p_month + """,'YYYYMM')- 1 MONTHS,'YYYYMM')
        AND A.ST_NO=B.STEELNO
        AND A.THICK_COD=B.THICK_CODE
        AND A.WIDTH_COD=B.WIDTH_CODE
        AND A.KEY_CODE=B.ENTRY_THICK_CODE
        WHERE
            A.WORK_DATE BETWEEN """ + P_START_TIME + """  AND """ + P_END_TIME + """ 
             AND A.EDITION_VALUE_FACT<>0 AND A.ST_NO IS NOT NULL AND A.EDITION_VALUE_FACT is not null
        AND A.EDITION IN ('CCL','DG_COST','FL')
        GROUP BY A.COST_CENTER,A.UNIT,
            A.ST_NO,
            A.THICK,
            A.THICK_COD,
            A.WIDTH,
            A.WIDTH_COD,
            A.KEY_VAL,
            A.KEY_CODE,A.EDITION,A.EDITION_UNIT,A.EDITION_NAME,
            B.TARGET_RANGE_UL,
            B.TARGET_RANGE_LL
    """

    df_result1 = util.query(conn_mpp, sql=sql1)
    util.insert(df_result1, conn_rds, "T_ADS_FACT_SICB_MO0102", "BGTARAS1")

    # Q602 值可以为0
    sql2 = """
        SELECT
    '1001'                AS ACCT,
    LEFT(A.COST_CENTER,2) AS DEPARTMENT_CODE,
    A.COST_CENTER         AS COST_CENTER,
    A.UNIT                AS UNIT_CODE,
    """ + p_month + """                 AS DATE,
    A.ST_NO               AS STEELNO,
    A.THICK,
    A.THICK_COD AS THICK_CODE,
    A.WIDTH,
    A.WIDTH_COD                                    AS WIDTH_CODE,
    A.KEY_VAL                                      AS ENTRY_THICK,
    A.KEY_CODE                                     AS ENTRY_THICK_CODE,
    A.EDITION                                      AS IND,
    A.EDITION_NAME                                 AS IND_NAME,
    A.EDITION_UNIT                                 AS IND_UNIT,
    ROUND(AVG(A.EDITION_VALUE_FACT),3)                INDEX_VALUE,
    B.TARGET_RANGE_UL                              AS TARGET_RANGE_UL,
    B.TARGET_RANGE_LL                              AS TARGET_RANGE_LL,
    'admin'                                        AS REC_REVISOR,
    TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME,
    'admin'                                        AS REC_CREATOR,
    TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
    ROUND(AVG(A.EDITION_VALUE_FACT),3)                REVISE_VALUE
FROM
    BGTAMAS1.V_SU_AJBG_MO0101 A
LEFT JOIN
    BGTAMAS1.T_ADS_FACT_SICB_MO0102 B
ON
    A.UNIT=B.UNIT_CODE
AND A.EDITION=B.IND
AND B.DATE=TO_CHAR(TO_DATE(""" + p_month + """ ,'YYYYMM')- 1 MONTHS,'YYYYMM')
AND A.ST_NO=B.STEELNO
AND A.THICK_COD=B.THICK_CODE
AND A.WIDTH_COD=B.WIDTH_CODE
AND A.KEY_CODE=B.ENTRY_THICK_CODE
WHERE
    A.UNIT='Q602'
AND A.WORK_DATE BETWEEN """ + P_START_TIME + """ 
AND """ + P_END_TIME + """ 
AND A.EDITION IN ('63000_COST',
                  '64300_COST',
                  '64400_X_COST',
                  '64400_YZ_COST',
                  'ADDWATER_COST',
                  'ELAPSED_TIME',
                  'MAIN_MOTER_CURRENT_1',
                  'MAIN_MOTER_CURRENT_2',
                  'MAIN_MOTER_CURRENT_3',
                  'MAIN_MOTER_CURRENT_4',
                  'MAIN_MOTER_CURRENT_5',
                  'MAIN_MOTER_CURRENT_6',
                  'MAIN_MOTER_CURRENT_7',
                  'MAIN_TANK_TEMP_1',
                  'REPAIR_WT',
                  'ROLL_SPEED_1',
                  'ROLL_SPEED_2',
                  'ROLL_SPEED_3',
                  'ROLL_SPEED_4',
                  'ROLL_SPEED_5',
                  'ROLL_SPEED_6',
                  'ROLL_SPEED_7',
                  'TANK_TEMP',
                  'TOTAL_LOST_WT')
AND A.EDITION_VALUE_FACT IS NOT NULL
AND A.ST_NO IS NOT NULL
GROUP BY
    A.COST_CENTER,
    A.UNIT,
    A.ST_NO,
    A.THICK,
    A.THICK_COD,
    A.WIDTH,
    A.WIDTH_COD,
    A.KEY_VAL,
    A.KEY_CODE,
    A.EDITION,
    A.EDITION_UNIT,
    A.EDITION_NAME,
    B.TARGET_RANGE_UL,
    B.TARGET_RANGE_LL;
        """
    df_result2 = util.query(conn_mpp, sql=sql2)
    util.insert(df_result2, conn_rds, "T_ADS_FACT_SICB_MO0102", "BGTARAS1")

    # 512 值可以为0
    sql3 = """
            SELECT
    '1001'                AS ACCT,
    LEFT(A.COST_CENTER,2) AS DEPARTMENT_CODE,
    A.COST_CENTER         AS COST_CENTER,
    A.UNIT                AS UNIT_CODE,
    """ + p_month + """                AS DATE,
    A.ST_NO               AS STEELNO,
    A.THICK,
    A.THICK_COD AS THICK_CODE,
    A.WIDTH,
    A.WIDTH_COD                                    AS WIDTH_CODE,
    A.KEY_VAL                                      AS ENTRY_THICK,
    A.KEY_CODE                                     AS ENTRY_THICK_CODE,
    A.EDITION                                      AS IND,
    A.EDITION_NAME                                 AS IND_NAME,
    A.EDITION_UNIT                                 AS IND_UNIT,
    ROUND(AVG(A.EDITION_VALUE_FACT),3)                INDEX_VALUE,
    B.TARGET_RANGE_UL                              AS TARGET_RANGE_UL,
    B.TARGET_RANGE_LL                              AS TARGET_RANGE_LL,
    'admin'                                        AS REC_REVISOR,
    TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME,
    'admin'                                        AS REC_CREATOR,
    TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
    ROUND(AVG(A.EDITION_VALUE_FACT),3)                REVISE_VALUE
FROM
    BGTAMAS1.V_SU_AJBG_MO0101 A
LEFT JOIN
    BGTAMAS1.T_ADS_FACT_SICB_MO0102 B
ON
    A.UNIT=B.UNIT_CODE
AND A.EDITION=B.IND
AND B.DATE=TO_CHAR(TO_DATE(""" + p_month + """,'YYYYMM')- 1 MONTHS,'YYYYMM')
AND A.ST_NO=B.STEELNO
AND A.THICK_COD=B.THICK_CODE
AND A.WIDTH_COD=B.WIDTH_CODE
AND A.KEY_CODE=B.ENTRY_THICK_CODE
WHERE
    A.UNIT='Q512'
AND A.WORK_DATE BETWEEN """ + P_START_TIME + """
AND """ + P_END_TIME + """
AND A.EDITION_VALUE_FACT IS NOT NULL
AND A.ST_NO IS NOT NULL
AND A.EDITION IN ('TOTAL_LOST_WT',
                  'SCRAP_WT_TAIL_OUT',
                  'SCRAP_WT_HEAD_OUT',
                  'REJ_LENG',
                  'MGO_VOL_RATIO',
                  'MGO_THICK',
                  'ESH_T_CROP_WGHT',
                  'ESH_H_CROP_WGHT',
                  'CT_AMNT_T_AVE',
                  'CT_AMNT_B_AVE')
GROUP BY
    A.COST_CENTER,
    A.UNIT,
    A.ST_NO,
    A.THICK,
    A.THICK_COD,
    A.WIDTH,
    A.WIDTH_COD,
    A.KEY_VAL,
    A.KEY_CODE,
    A.EDITION,
    A.EDITION_UNIT,
    A.EDITION_NAME,
    B.TARGET_RANGE_UL,
    B.TARGET_RANGE_LL
            """
    df_result3 = util.query(conn_mpp, sql=sql3)
    util.insert(df_result3, conn_rds, "T_ADS_FACT_SICB_MO0102", "BGTARAS1")

    # 503
    sql4 = """
                SELECT
        '1001'                AS ACCT,
        LEFT(A.COST_CENTER,2) AS DEPARTMENT_CODE,
        A.COST_CENTER         AS COST_CENTER,
        A.UNIT                AS UNIT_CODE,
        """ + p_month + """                AS DATE,
        A.ST_NO               AS STEELNO,
        A.THICK,
        A.THICK_COD AS THICK_CODE,
        A.WIDTH,
        A.WIDTH_COD                                    AS WIDTH_CODE,
        A.KEY_VAL                                      AS ENTRY_THICK,
        A.KEY_CODE                                     AS ENTRY_THICK_CODE,
        A.EDITION                                      AS IND,
        A.EDITION_NAME                                 AS IND_NAME,
        A.EDITION_UNIT                                 AS IND_UNIT,
        ROUND(AVG(A.EDITION_VALUE_FACT),3)                INDEX_VALUE,
        B.TARGET_RANGE_UL                              AS TARGET_RANGE_UL,
        B.TARGET_RANGE_LL                              AS TARGET_RANGE_LL,
        'admin'                                        AS REC_REVISOR,
        TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME,
        'admin'                                        AS REC_CREATOR,
        TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
        ROUND(AVG(A.EDITION_VALUE_FACT),3)                REVISE_VALUE
    FROM
        BGTAMAS1.V_SU_AJBG_MO0101 A
    LEFT JOIN
        BGTAMAS1.T_ADS_FACT_SICB_MO0102 B
    ON
        A.UNIT=B.UNIT_CODE
    AND A.EDITION=B.IND
    AND B.DATE=TO_CHAR(TO_DATE(""" + p_month + """,'YYYYMM')- 1 MONTHS,'YYYYMM')
    AND A.ST_NO=B.STEELNO
    AND A.THICK_COD=B.THICK_CODE
    AND A.WIDTH_COD=B.WIDTH_CODE
    AND A.KEY_CODE=B.ENTRY_THICK_CODE
    WHERE
        A.UNIT='Q503'
    AND A.WORK_DATE BETWEEN """ + P_START_TIME + """
    AND """ + P_END_TIME + """
    AND A.EDITION_VALUE_FACT IS NOT NULL
    AND A.ST_NO IS NOT NULL
    AND A.EDITION IN ('GAS_FLOW',
                  'N2_FLOW',
                  'H2_FLOW',
                  'FWTEMP_PVOUT10_2',
                  'FWTEMP_PVOUT10_1',
                  'FWTEMP_PVIN10_2',
                  'FWTEMP_PVIN10_1',
                  'BDOOR_SEAL_TEMP',
                  'FUEL_GAS_TEMP',
                  'CDOOR_SEAL_TEMP',
                  'EX_GAS_ENTRY_TEMP',
                  'EX_GAS_EXIT_TEMP',
                  'FPRESSURE_PV3',
                  'FPRESSURE_SV1',
                  'PURE_WATER_FLOW',
                  'CIRC_WATER_FLOW',
                  'INDUSTRY_WATER_FLOW',
                  'STEAM_FLOW',
                  'ESH_H_CROP_WGHT',
                  'ESH_T_CROP_WGHT',
                  'REJ_LENG')
    GROUP BY
        A.COST_CENTER,
        A.UNIT,
        A.ST_NO,
        A.THICK,
        A.THICK_COD,
        A.WIDTH,
        A.WIDTH_COD,
        A.KEY_VAL,
        A.KEY_CODE,
        A.EDITION,
        A.EDITION_UNIT,
        A.EDITION_NAME,
        B.TARGET_RANGE_UL,
        B.TARGET_RANGE_LL
                """
    df_result4 = util.query(conn_mpp, sql=sql4)
    util.insert(df_result4, conn_rds, "T_ADS_FACT_SICB_MO0102", "BGTARAS1")

    # 414
    sql5 = """
                    SELECT
            '1001'                AS ACCT,
            LEFT(A.COST_CENTER,2) AS DEPARTMENT_CODE,
            A.COST_CENTER         AS COST_CENTER,
            A.UNIT                AS UNIT_CODE,
            """ + p_month + """                AS DATE,
            A.ST_NO               AS STEELNO,
            A.THICK,
            A.THICK_COD AS THICK_CODE,
            A.WIDTH,
            A.WIDTH_COD                                    AS WIDTH_CODE,
            A.KEY_VAL                                      AS ENTRY_THICK,
            A.KEY_CODE                                     AS ENTRY_THICK_CODE,
            A.EDITION                                      AS IND,
            A.EDITION_NAME                                 AS IND_NAME,
            A.EDITION_UNIT                                 AS IND_UNIT,
            ROUND(AVG(A.EDITION_VALUE_FACT),3)                INDEX_VALUE,
            B.TARGET_RANGE_UL                              AS TARGET_RANGE_UL,
            B.TARGET_RANGE_LL                              AS TARGET_RANGE_LL,
            'admin'                                        AS REC_REVISOR,
            TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME,
            'admin'                                        AS REC_CREATOR,
            TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
            ROUND(AVG(A.EDITION_VALUE_FACT),3)                REVISE_VALUE
        FROM
            BGTAMAS1.V_SU_AJBG_MO0101 A
        LEFT JOIN
            BGTAMAS1.T_ADS_FACT_SICB_MO0102 B
        ON
            A.UNIT=B.UNIT_CODE
        AND A.EDITION=B.IND
        AND B.DATE=TO_CHAR(TO_DATE(""" + p_month + """,'YYYYMM')- 1 MONTHS,'YYYYMM')
        AND A.ST_NO=B.STEELNO
        AND A.THICK_COD=B.THICK_CODE
        AND A.WIDTH_COD=B.WIDTH_CODE
        AND A.KEY_CODE=B.ENTRY_THICK_CODE
        WHERE
            A.UNIT='Q414'
        AND A.WORK_DATE BETWEEN """ + P_START_TIME + """
        AND """ + P_END_TIME + """
        AND A.EDITION_VALUE_FACT IS NOT NULL
        AND A.ST_NO IS NOT NULL
        AND A.EDITION IN ('ESH_T_CROP_WGHT',
                  'REJ_LENG',
                  'SCRAP_WT_TAIL_OUT',
                  'ESH_H_CROP_WGHT',
                  'SCRAP_WT_HEAD_OUT',
                  'TRIM_SCRAP_WIDTH_DS',
                  'TRIM_SCRAP_WIDTH_WS',
                  'MGO_WT',
                  'COAT_WT_DIFF',
                  'ATC_Z1_ENERGY',
                  'ATC_Z2_ENERGY',
                  'HOT_RINSE3_TEMP_PV',
                  'FURNACE_PRESS_ESD1',
                  'FURNACE_PRESS_RTF',
                  'FURNACE_PRESS_SF',
                  'FURNACE_PRESS_ATC',
                  'FURNACE_PRESS_RJC',
                  'FURNACE_PRESS_ESD2',
                  'SF_H2_PV',
                  'RTF_H2_PV',
                  'MIX_STATION_H2_PV')
        GROUP BY
            A.COST_CENTER,
            A.UNIT,
            A.ST_NO,
            A.THICK,
            A.THICK_COD,
            A.WIDTH,
            A.WIDTH_COD,
            A.KEY_VAL,
            A.KEY_CODE,
            A.EDITION,
            A.EDITION_UNIT,
            A.EDITION_NAME,
            B.TARGET_RANGE_UL,
            B.TARGET_RANGE_LL
                    """
    df_result5 = util.query(conn_mpp, sql=sql5)
    util.insert(df_result5, conn_rds, "T_ADS_FACT_SICB_MO0102", "BGTARAS1")

    # 412
    sql6 = """
                        SELECT
                '1001'                AS ACCT,
                LEFT(A.COST_CENTER,2) AS DEPARTMENT_CODE,
                A.COST_CENTER         AS COST_CENTER,
                A.UNIT                AS UNIT_CODE,
                """ + p_month + """                AS DATE,
                A.ST_NO               AS STEELNO,
                A.THICK,
                A.THICK_COD AS THICK_CODE,
                A.WIDTH,
                A.WIDTH_COD                                    AS WIDTH_CODE,
                A.KEY_VAL                                      AS ENTRY_THICK,
                A.KEY_CODE                                     AS ENTRY_THICK_CODE,
                A.EDITION                                      AS IND,
                A.EDITION_NAME                                 AS IND_NAME,
                A.EDITION_UNIT                                 AS IND_UNIT,
                ROUND(AVG(A.EDITION_VALUE_FACT),3)                INDEX_VALUE,
                B.TARGET_RANGE_UL                              AS TARGET_RANGE_UL,
                B.TARGET_RANGE_LL                              AS TARGET_RANGE_LL,
                'admin'                                        AS REC_REVISOR,
                TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME,
                'admin'                                        AS REC_CREATOR,
                TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                ROUND(AVG(A.EDITION_VALUE_FACT),3)                REVISE_VALUE
            FROM
                BGTAMAS1.V_SU_AJBG_MO0101 A
            LEFT JOIN
                BGTAMAS1.T_ADS_FACT_SICB_MO0102 B
            ON
                A.UNIT=B.UNIT_CODE
            AND A.EDITION=B.IND
            AND B.DATE=TO_CHAR(TO_DATE(""" + p_month + """,'YYYYMM')- 1 MONTHS,'YYYYMM')
            AND A.ST_NO=B.STEELNO
            AND A.THICK_COD=B.THICK_CODE
            AND A.WIDTH_COD=B.WIDTH_CODE
            AND A.KEY_CODE=B.ENTRY_THICK_CODE
            WHERE
                A.UNIT='Q412'
            AND A.WORK_DATE BETWEEN """ + P_START_TIME + """
            AND """ + P_END_TIME + """
            AND A.EDITION_VALUE_FACT IS NOT NULL
            AND A.ST_NO IS NOT NULL
            AND A.EDITION IN ('MGO_VOL_RATIO',
                  'SCRAP_WT_HEAD_OUT',
                  'SCRAP_WT_TAIL_OUT',
                  'TOTAL_LOST_WT',
                  'MGO_THICK',
                  'ESH_H_CROP_WGHT',
                  'ESH_T_CROP_WGHT',
                  'REJ_LENG')
            GROUP BY
                A.COST_CENTER,
                A.UNIT,
                A.ST_NO,
                A.THICK,
                A.THICK_COD,
                A.WIDTH,
                A.WIDTH_COD,
                A.KEY_VAL,
                A.KEY_CODE,
                A.EDITION,
                A.EDITION_UNIT,
                A.EDITION_NAME,
                B.TARGET_RANGE_UL,
                B.TARGET_RANGE_LL
                        """
    df_result6 = util.query(conn_mpp, sql=sql6)
    util.insert(df_result6, conn_rds, "T_ADS_FACT_SICB_MO0102", "BGTARAS1")

    # 314
    sql7 = """
                            SELECT
                    '1001'                AS ACCT,
                    LEFT(A.COST_CENTER,2) AS DEPARTMENT_CODE,
                    A.COST_CENTER         AS COST_CENTER,
                    A.UNIT                AS UNIT_CODE,
                    """ + p_month + """                AS DATE,
                    A.ST_NO               AS STEELNO,
                    A.THICK,
                    A.THICK_COD AS THICK_CODE,
                    A.WIDTH,
                    A.WIDTH_COD                                    AS WIDTH_CODE,
                    A.KEY_VAL                                      AS ENTRY_THICK,
                    A.KEY_CODE                                     AS ENTRY_THICK_CODE,
                    A.EDITION                                      AS IND,
                    A.EDITION_NAME                                 AS IND_NAME,
                    A.EDITION_UNIT                                 AS IND_UNIT,
                    ROUND(AVG(A.EDITION_VALUE_FACT),3)                INDEX_VALUE,
                    B.TARGET_RANGE_UL                              AS TARGET_RANGE_UL,
                    B.TARGET_RANGE_LL                              AS TARGET_RANGE_LL,
                    'admin'                                        AS REC_REVISOR,
                    TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME,
                    'admin'                                        AS REC_CREATOR,
                    TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                    ROUND(AVG(A.EDITION_VALUE_FACT),3)                REVISE_VALUE
                FROM
                    BGTAMAS1.V_SU_AJBG_MO0101 A
                LEFT JOIN
                    BGTAMAS1.T_ADS_FACT_SICB_MO0102 B
                ON
                    A.UNIT=B.UNIT_CODE
                AND A.EDITION=B.IND
                AND B.DATE=TO_CHAR(TO_DATE(""" + p_month + """,'YYYYMM')- 1 MONTHS,'YYYYMM')
                AND A.ST_NO=B.STEELNO
                AND A.THICK_COD=B.THICK_CODE
                AND A.WIDTH_COD=B.WIDTH_CODE
                AND A.KEY_CODE=B.ENTRY_THICK_CODE
                WHERE
                    A.UNIT='Q314'
                AND A.WORK_DATE BETWEEN """ + P_START_TIME + """
                AND """ + P_END_TIME + """
                AND A.EDITION_VALUE_FACT IS NOT NULL
                AND A.ST_NO IS NOT NULL
                AND A.EDITION IN ('COAT_WT_DIFF',
                  'ESH_H_CROP_WGHT',
                  'ESH_T_CROP_WGHT',
                  'FURNACE_PRESS_ATC',
                  'FURNACE_PRESS_ESD1',
                  'FURNACE_PRESS_ESD2' ,
                  'FURNACE_PRESS_RTF',
                  'MGO_WT',
                  'MIX_STATION_H2_PV',
                  'REJ_LENG',
                  'SCRAP_WT_HEAD_OUT',
                  'SCRAP_WT_TAIL_OUT',
                  'TRIM_SCRAP_WIDTH_DS',
                  'TRIM_SCRAP_WIDTH_WS',
                  'FURNACE_PRESS_RJC',
                  'FURNACE_PRESS_SF',
                  'SF_H2_PV',
                  'RTF_H2_PV')
                GROUP BY
                    A.COST_CENTER,
                    A.UNIT,
                    A.ST_NO,
                    A.THICK,
                    A.THICK_COD,
                    A.WIDTH,
                    A.WIDTH_COD,
                    A.KEY_VAL,
                    A.KEY_CODE,
                    A.EDITION,
                    A.EDITION_UNIT,
                    A.EDITION_NAME,
                    B.TARGET_RANGE_UL,
                    B.TARGET_RANGE_LL
                            """
    df_result7 = util.query(conn_mpp, sql=sql7)
    util.insert(df_result7, conn_rds, "T_ADS_FACT_SICB_MO0102", "BGTARAS1")

    # 502 值可以为0
    sql8 = """
                                SELECT
                        '1001'                AS ACCT,
                        LEFT(A.COST_CENTER,2) AS DEPARTMENT_CODE,
                        A.COST_CENTER         AS COST_CENTER,
                        A.UNIT                AS UNIT_CODE,
                        """ + p_month + """                AS DATE,
                        A.ST_NO               AS STEELNO,
                        A.THICK,
                        A.THICK_COD AS THICK_CODE,
                        A.WIDTH,
                        A.WIDTH_COD                                    AS WIDTH_CODE,
                        A.KEY_VAL                                      AS ENTRY_THICK,
                        A.KEY_CODE                                     AS ENTRY_THICK_CODE,
                        A.EDITION                                      AS IND,
                        A.EDITION_NAME                                 AS IND_NAME,
                        A.EDITION_UNIT                                 AS IND_UNIT,
                        ROUND(AVG(A.EDITION_VALUE_FACT),3)                INDEX_VALUE,
                        B.TARGET_RANGE_UL                              AS TARGET_RANGE_UL,
                        B.TARGET_RANGE_LL                              AS TARGET_RANGE_LL,
                        'admin'                                        AS REC_REVISOR,
                        TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME,
                        'admin'                                        AS REC_CREATOR,
                        TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                        ROUND(AVG(A.EDITION_VALUE_FACT),3)                REVISE_VALUE
                    FROM
                        BGTAMAS1.V_SU_AJBG_MO0101 A
                    LEFT JOIN
                        BGTAMAS1.T_ADS_FACT_SICB_MO0102 B
                    ON
                        A.UNIT=B.UNIT_CODE
                    AND A.EDITION=B.IND
                    AND B.DATE=TO_CHAR(TO_DATE(""" + p_month + """,'YYYYMM')- 1 MONTHS,'YYYYMM')
                    AND A.ST_NO=B.STEELNO
                    AND A.THICK_COD=B.THICK_CODE
                    AND A.WIDTH_COD=B.WIDTH_CODE
                    AND A.KEY_CODE=B.ENTRY_THICK_CODE
                    WHERE
                        A.UNIT='Q502'
                    AND A.WORK_DATE BETWEEN """ + P_START_TIME + """
                    AND """ + P_END_TIME + """
                    AND A.EDITION_VALUE_FACT IS NOT NULL
                    AND A.ST_NO IS NOT NULL
                    AND A.EDITION IN ( '63000_COST',
                  '64300_COST',
                  '64400_X_COST',
                  '64400_YZ_COST',
                  'ADDWATER_COST',
                  'ELAPSED_TIME',
                  'EMUC_OIL',
                  'MAIN_MOTOR_POWER_1',
                  'MAIN_MOTOR_POWER_2',
                  'MAIN_MOTOR_POWER_3',
                  'MAIN_MOTOR_POWER_4',
                  'MAIN_MOTOR_POWER_5',
                  'MAIN_MOTOR_POWER_6',
                  'MAIN_MOTOR_POWER_7',
                  'MAIN_MOTOR_POWER_8',
                  'MAIN_MOTOR_POWER_9',
                  'MAIN_TANK_TEMP_1',
                  'PROFIT_WT',
                  'REPAIR_WT',
                  'ROLL_NUM_1',
                  'ROLL_SPEED_1',
                  'ROLL_SPEED_2',
                  'ROLL_SPEED_3',
                  'ROLL_SPEED_4',
                  'ROLL_SPEED_5',
                  'ROLL_SPEED_6',
                  'ROLL_SPEED_7',
                  'ROLL_SPEED_8',
                  'ROLL_SPEED_9',
                  'SUBSTD_WT',
                  'TANK_NO',
                  'TANK_TEMP',
                  'TOTAL_LOST_WT',
                  'WASTE_WT')
                    GROUP BY
                        A.COST_CENTER,
                        A.UNIT,
                        A.ST_NO,
                        A.THICK,
                        A.THICK_COD,
                        A.WIDTH,
                        A.WIDTH_COD,
                        A.KEY_VAL,
                        A.KEY_CODE,
                        A.EDITION,
                        A.EDITION_UNIT,
                        A.EDITION_NAME,
                        B.TARGET_RANGE_UL,
                        B.TARGET_RANGE_LL
                                """
    df_result8 = util.query(conn_mpp, sql=sql8)
    util.insert(df_result8, conn_rds, "T_ADS_FACT_SICB_MO0102", "BGTARAS1")

    # 402 值可以为0
    sql9 = """
                                    SELECT
                            '1001'                AS ACCT,
                            LEFT(A.COST_CENTER,2) AS DEPARTMENT_CODE,
                            A.COST_CENTER         AS COST_CENTER,
                            A.UNIT                AS UNIT_CODE,
                            """ + p_month + """                AS DATE,
                            A.ST_NO               AS STEELNO,
                            A.THICK,
                            A.THICK_COD AS THICK_CODE,
                            A.WIDTH,
                            A.WIDTH_COD                                    AS WIDTH_CODE,
                            A.KEY_VAL                                      AS ENTRY_THICK,
                            A.KEY_CODE                                     AS ENTRY_THICK_CODE,
                            A.EDITION                                      AS IND,
                            A.EDITION_NAME                                 AS IND_NAME,
                            A.EDITION_UNIT                                 AS IND_UNIT,
                            ROUND(AVG(A.EDITION_VALUE_FACT),3)                INDEX_VALUE,
                            B.TARGET_RANGE_UL                              AS TARGET_RANGE_UL,
                            B.TARGET_RANGE_LL                              AS TARGET_RANGE_LL,
                            'admin'                                        AS REC_REVISOR,
                            TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME,
                            'admin'                                        AS REC_CREATOR,
                            TO_CHAR(CURRENT TIMESTAMP, 'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                            ROUND(AVG(A.EDITION_VALUE_FACT),3)                REVISE_VALUE
                        FROM
                            BGTAMAS1.V_SU_AJBG_MO0101 A
                        LEFT JOIN
                            BGTAMAS1.T_ADS_FACT_SICB_MO0102 B
                        ON
                            A.UNIT=B.UNIT_CODE
                        AND A.EDITION=B.IND
                        AND B.DATE=TO_CHAR(TO_DATE(""" + p_month + """,'YYYYMM')- 1 MONTHS,'YYYYMM')
                        AND A.ST_NO=B.STEELNO
                        AND A.THICK_COD=B.THICK_CODE
                        AND A.WIDTH_COD=B.WIDTH_CODE
                        AND A.KEY_CODE=B.ENTRY_THICK_CODE
                        WHERE
                            A.UNIT='Q402'
                        AND A.WORK_DATE BETWEEN """ + P_START_TIME + """
                        AND """ + P_END_TIME + """
                        AND A.EDITION_VALUE_FACT IS NOT NULL
                        AND A.ST_NO IS NOT NULL
                        AND A.EDITION IN ( '63000_COST',
                  '64300_COST',
                  '64400_X_COST',
                  '64400_YZ_COST',
                  'ADDWATER_COST',
                  'ELAPSED_TIME',
                  'EMUC_OIL',
                  'MAIN_MOTOR_POWER_1',
                  'MAIN_MOTOR_POWER_2',
                  'MAIN_MOTOR_POWER_3',
                  'MAIN_MOTOR_POWER_4',
                  'MAIN_MOTOR_POWER_5',
                  'MAIN_MOTOR_POWER_6',
                  'MAIN_MOTOR_POWER_7',
                  'MAIN_MOTOR_POWER_8',
                  'MAIN_MOTOR_POWER_9',
                  'MAIN_TANK_TEMP_1',
                  'PROFIT_WT',
                  'REPAIR_WT',
                  'ROLL_NUM_1',
                  'ROLL_SPEED_1',
                  'ROLL_SPEED_2',
                  'ROLL_SPEED_3',
                  'ROLL_SPEED_4',
                  'ROLL_SPEED_5',
                  'ROLL_SPEED_6',
                  'ROLL_SPEED_7',
                  'ROLL_SPEED_8',
                  'ROLL_SPEED_9',
                  'SUBSTD_WT',
                  'TANK_NO',
                  'TANK_TEMP',
                  'TOTAL_LOST_WT',
                  'WASTE_WT')
                        GROUP BY
                            A.COST_CENTER,
                            A.UNIT,
                            A.ST_NO,
                            A.THICK,
                            A.THICK_COD,
                            A.WIDTH,
                            A.WIDTH_COD,
                            A.KEY_VAL,
                            A.KEY_CODE,
                            A.EDITION,
                            A.EDITION_UNIT,
                            A.EDITION_NAME,
                            B.TARGET_RANGE_UL,
                            B.TARGET_RANGE_LL
                                    """
    df_result9 = util.query(conn_mpp, sql=sql9)
    util.insert(df_result9, conn_rds, "T_ADS_FACT_SICB_MO0102", "BGTARAS1")


def MO0102(p_month):
    global conn_rds,conn_mpp
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    conn_mpp = util.getConnectionDb2('10.70.49.21', '50021 ', 'BGBDPROD', 'bgmoq000', 'bgmoq000bgta')
    print(datetime.datetime.now())
    insertMO0102(p_month)
    print(datetime.datetime.now())
    conn_rds.close()


def main():
    MO0102('202111')


if __name__ == '__main__':
    main()
